--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_FLOAT_LOAF.HQL
--    Functions : 表4：贷款发生额利率是否固定期限结构表
--    Purpose   : 
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_FLOAT_LOAF PARTITION (DATA_DATE = '#V_DATA_DATE#')
    -- 除了合计之外的产品类型
    SELECT
     AREA.AREA_CODE_4                                                                            AS FIN_ORG_DIST
    ,FSE.FIN_ORG_NO                                                                              AS FIN_ORG_NO
    ,FSE.CCY                                                                                     AS CCY
    ,FSE.IF_FLOAT                                                                                AS IF_FLOAT            -- 利率是否固定
    ,CASE WHEN FSE.IF_FLOAT='RF01' THEN '固定利率'
          WHEN FSE.IF_FLOAT='RF02' THEN '浮动利率'
     END                                                                                         AS FLOAT_DESC
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_6_12M           --6个月至1年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_1_3Y            --1至3年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,SUM(COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_10_999Y        --10年以上-加权金额
    ,SUM(COALESCE(FSE.OCCUR_AMOUNT,0))/100000000                                                 AS AMOUNT_ALL          --所有期限-贷款余额
    ,SUM(COALESCE(FSE.WSUM_AMOUNT,0))/100000000                                                  AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND DATA_DATE='#V_DATA_DATE#')FSE -- 只统计发放
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON FSE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY AREA.AREA_CODE_4
            ,FSE.FIN_ORG_NO
            ,FSE.CCY
            ,FSE.IF_FLOAT
            ,CASE WHEN FSE.IF_FLOAT='RF01' THEN '固定利率'
                  WHEN FSE.IF_FLOAT='RF02' THEN '浮动利率'
            END
        
    UNION ALL

    -- 合计
    SELECT
     AREA.AREA_CODE_4                                                                              AS FIN_ORG_DIST
    ,LOAF.FIN_ORG_NO                                                                               AS FIN_ORG_NO
    ,LOAF.CCY                                                                                      AS CCY
    ,'Z'                                                                                           AS BUSINESS_TYPE
    ,'合计'                                                                                        AS BUSINESS_DESC
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_0_6M            --6个月（含）以内-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_6_12M           --6个月至1年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_1_3Y            --1至3年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_3_5Y            --3至5年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_5_10Y           --5至10年（含）-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0))/100000000   AS AMOUNT_10_999Y         --10年以上-贷款余额
    ,SUM(COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0))/100000000   AS WSUM_10_999Y        --10年以上-加权金额
    ,SUM(COALESCE(LOAF.OCCUR_AMOUNT,0))/100000000                                                  AS AMOUNT_ALL             --所有期限-贷款余额
    ,SUM(COALESCE(LOAF.WSUM_AMOUNT,0))/100000000                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DRAWDOWN_OR_REPAY='1' AND DATA_DATE='#V_DATA_DATE#') LOAF -- 只统计发放
   -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON LOAF.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    WHERE LOAF.DATA_DATE='#V_DATA_DATE#'
    GROUP BY AREA.AREA_CODE_4,LOAF.FIN_ORG_NO,LOAF.CCY
;